<?php


namespace backend\modules\statistics\controllers;
use EasyWeChat\Message\Text;
use backend\controllers\AdminController;
use common\models\business\BusinessDisease;
use common\models\business\BusinessDiseaseType;
use common\models\business\BusinessException;
use common\models\business\BusinessInspection;
use common\models\business\BusinessPerson;
use common\models\business\BusinessVaccine;
use common\models\business\BusinessPig;
use common\models\business\BusinessVaccineSet;
use common\models\business\NewBusinessInspection;
use common\models\business\NewBusinessInspectionData;
use common\models\sys\SysSetting;
use function MongoDB\BSON\fromJSON;
use yii\db\Expression;
use yii\db\Query;
use yii\helpers\ArrayHelper;

/**
 * Class SiteController
 * @package backend\modules\finance\controllers
 * 仔猪相关统计
 */
class PigController extends AdminController
{


    //仔猪状态统计
    public function actionIndex()
    {

        //仔猪状态总体统计图
        $list = (new Query())->select(['status','count(id) as num'])->from(BusinessPig::tableName())->groupBy('status')->indexBy('status')->all();
        foreach ($list as $key => $row){
            $row['status'] = BusinessPig::$statues[$row['status']];
            $statusdata['type'][] = $row['status'];
            $statusdata['num'][] = $row['num'];
        }


        $query = (new Query())->select('status,batch')->from(BusinessPig::tableName());

        //取出每个批次的总数
        $total = (new Query())->select('count(*) as total,batch')->from(BusinessPig::tableName())->groupBy('batch')->indexBy('batch')->all();



        //每个批次每种状态分别存放
        foreach ($query->each() as $key => $row) {
            $all[$row['batch']][$row['status']][] = $row;
        }

        $reslut = [];
        foreach ($all as $key => $row){
            foreach (BusinessPig::$statues as $k => $v){        //查看每种状态的数目
                if(array_key_exists($k,$row)){
                    $reslut[$key][$k]['num'] = count($row[$k]);
                }else{
                    $reslut[$key][$k]['num'] = 0;
                }
                $reslut[$key]['total'] = $total[$key]['total'];
                $reslut[$key][$k]['name'] = $v;
            }
        }
        unset($all,$list);


        return $this->render('index',[
            'statusdata' => $statusdata,
            'result' => $reslut,
        ]);
    }


    public function actionGetStatusnum($batch,$status,$outtime)
    {

        $statues = array_flip(BusinessPig::$statues);

        //出栏预警的体重
        $outweight = 130;

        if($status == '预出栏'){
            $list = (new \yii\db\Query())->select('p.*')
                ->from(['p'=>\common\models\business\BusinessPig::tableName()])
                ->where('planout != 0 and planout<=:preout and batch=:batch',[':preout'=>$outtime,':batch'=>$batch])
                ->andWhere([
                    'id'=>(new \yii\db\Query())->select('ins.pigid')
                        ->from(['ins'=>NewBusinessInspection::tableName()])
                        ->leftJoin(['insd'=>NewBusinessInspectionData::tableName()],'inspectionid=ins.id')
                        ->where('insd.fieldname=\'体重\' and convert(insd.val,decimal(10,2))>=:outweight',[':outweight'=>$outweight])
                        ->orderBy('ins.createtime desc')
                        ->scalar()
                ])
                ->all();
        }else{
            $list = (new Query())->select('*')->from(BusinessPig::tableName())
                ->where('batch=:batch and status=:status',[':batch'=>$batch,':status'=>$statues[$status]])
                ->all();
        }
        return $this->renderPartial('_statusnum',[
            'list' => $list,
        ]);
    }



    //重量分布
    public function actionWeight()
    {

        //取出最后一次巡检的记录
        $pigs = (new Query())->select('pigid,i.id,max(val) as val,batch')
            ->from(NewBusinessInspection::tableName().' as i')
            ->leftJoin(NewBusinessInspectionData::tableName().' as idat','i.id=idat.inspectionid')
            ->leftJoin(BusinessPig::tableName().' as pig','i.pigid=pig.id')
            ->where('idat.fieldname=\'体重\'')
            ->groupBy('pigid')
            ->all();


        //取出体重分布数字

        $getweight = \Yii::$app->request->get('weight');
        if(empty($getweight)){
            $weight = [10,20,30,40,50];
        }else{
            $weighttmp = explode(',',$getweight);
            foreach ($weighttmp as $k => $value){
                $weight[] = $value;
            }
        }
        $length = count($weight);


        for ($i=0;$i<$length;$i++){
            if($i == 0){       //第一个
                $tabname[] = '小于'.$weight[0].'kg';
                $tab[] = $weight[0];
            }else if($i != ($length-1) && $i != 0) {     //在范围内
                $tabname[] = '介于'.$weight[$i].'kg-'.$weight[$i+1].'kg之间';
                $tab[] = $weight[$i].'-'.$weight[$i+1];
            }else if($i == ($length-1)){
                $tabname[] = '大于'.$weight[$length-1];
                $tab[] = $weight[$length-1];
            }
        }


        //按批次取出总数
        $total = (new  Query())->select('count(*) as total,batch')->from(BusinessPig::tableName())->groupBy('batch')->indexBy('batch')->all();


        //按批数将仔猪分开
        $wiarr = [];
        foreach ($pigs as $k => $v){
            if(empty($v['batch'])){
                continue;
            }
            $v['val'] = floatval($v['val']);
            for ($i=0;$i<$length;$i++){
                if($v['val'] <= $weight[0]){
                    $wiarr[$v['batch']][$weight[0]][] = $v;
                    break;
                }else if(($i != 0) && ($i != ($length-1)) && ($v['val'] > $weight[$i]) && ($v['val'] <= $weight[$i+1])) {     //在范围内
                    $wiarr[$v['batch']][$weight[$i].'-'.$weight[$i+1]][] = $v;
                    break;
                }else if($v['val'] >= $weight[$length-1]){
                    $wiarr[$v['batch']][$weight[$length-1]][] = $v;
                    break;
                }
            }
        }
        return $this->render('weight',[
            'wiarr' => $wiarr,
            'tabname' => $tabname,
            'tab' => $tab,
            'total' => $total
        ]);


    }


    /**
     * 猪仔生长异常统计
     * 最后的一次巡检与上上次的巡检重量进行对比，判断增量是否异常
     */
    public function actionGrow()
    {
        //异常值
        $expection = 10;

       $sql = 'select idat.val,i.pigid,pig.batch,i.id,createtime,pig.headperson from '.NewBusinessInspection::tableName().' as i 
                    left join '.NewBusinessInspectionData::tableName().' as idat on i.id=idat.inspectionid 
                    left join '.BusinessPig::tableName().' as pig on pig.id=i.pigid 
                    where idat.fieldname=\'体重\' and exists (select count(*) from '.NewBusinessInspection::tableName().' where pigid = i.pigid and id > i.id having count(*) < 2) order by i.pigid desc';


        $list = \Yii::$app->db->createCommand($sql)->queryAll();
        $pigs = [];
        foreach ($list as $key => $row){
            $pigs[$row['batch']][$row['pigid']][] = $row;
            $pigs[$row['batch']]['num'] = 0;
        }
        $num = [];
        foreach ($pigs as $key => $row){
            $pigs[$key]['num'] = 0;
            foreach ($row as $k => $v){
                if(count($v) == 2){
                    if(floatval($v[1]['val'])-floatval($v[0]['val'])<$expection){
                        $pigs[$key][$k]['isexpec'] = true;
                        $pigs[$key]['num']++;
                    }else{
                        $pigs[$key][$k]['isexpec'] = false;
                    }
                }
            }
        }


        $batchlist = (new Query())->select('count(*) as total,batch')->from(BusinessPig::tableName())
            ->groupBy('batch')
            ->indexBy('batch')
            ->all();


        return $this->render('grow',[
            'pigs' => $pigs,
            'batchlist' => $batchlist
        ]);

    }





    /**
     * 巡检异常统计
     */
    public function actionInspection()
    {
        
        //先取出两次巡检差距大于规定值的
        $gday = SysSetting::getValue('inspection_cycle');
        $gtime = $gday*3600*24;
        $now = time();
        $difftime = $now-$gtime;

        //取出已经巡检过的仔猪
        $sql = 'SELECT
                    (select count(*) from business_pig where headpersonid=business_personid and status=:status) as total,
                    count(*) as num,
                    business_personid,
                    business_person,
                    FROM_UNIXTIME(createtime,"%Y-%m-%d") as createtime
              FROM business_pig as pig left join
                    (
                        SELECT
                            pigid,max(createtime) as createtime,business_personid,business_person
                        FROM
                            new_business_inspection
                        GROUP BY 
                          pigid
                        ORDER BY
                            max(createtime) DESC
                    ) AS instmp on instmp.pigid=pig.id
                WHERE
                    createtime < :endtime
                AND (pig.status=:status)
                GROUP BY
                    pig.headpersonid
                ORDER BY
                    createtime DESC';

        $list = \Yii::$app->db->createCommand($sql)->bindValues([':status'=>BusinessPig::STATUS_AFTERIN,':endtime'=>$difftime])->queryAll();

//        var_dump($list);exit;
        $sql = 'select count(*) as num,headpersonid as business_personid,headperson as business_person from '.BusinessPig::tableName().' as pig where pig.status=:status and !exists(select id from '.NewBusinessInspection::tableName().' as ins where ins.pigid=pig.id) group by headpersonid';

        $list2 = \Yii::$app->db->createCommand($sql)->bindValues([':status'=>BusinessPig::STATUS_AFTERIN])->queryAll();

        $hasins = ArrayHelper::index($list, 'business_personid');
        $noins = ArrayHelper::index($list2, 'business_personid');



        //取出每个人管理的仔猪总数(是以入栏的)
        $total = (new  Query())->select('count(*) as total,headpersonid')
            ->from(BusinessPig::tableName())
            ->where('status=:status',[':status'=>BusinessPig::STATUS_AFTERIN])
            ->groupBy('headpersonid')
            ->indexBy('headpersonid')
            ->all();

        $recycle_day = SysSetting::getValue('inspection_cycle');

        return $this->render('inspection',[
            'list' => $hasins,
            'list2' => $noins,
            'total' => $total,
            'recycle_day' =>$recycle_day
        ]);

    }


    /**
     * @return string
     * 巡检异常详情
     */
    public function actionHasInspectionDetail($personid)
    {
        $this->layout = false;

        //先取出两次巡检差距大于规定值的
        $gday = SysSetting::getValue('inspection_cycle');
        $gtime = $gday*3600*24;
        $now = time();
        $difftime = $now-$gtime;
        //取出已经巡检过的仔猪
        $sql = 'SELECT
                    business_personid,
                    business_person,
                    pig.no,
                    pig.batch,
                    pig.liveryname,
                    FROM_UNIXTIME(createtime,"%Y-%m-%d") as createtime
              FROM business_pig as pig left join
                    (
                        SELECT
                            *
                        FROM
                            new_business_inspection
                        GROUP BY 
                          pigid
                        ORDER BY
                            createtime DESC
                    ) AS instmp on instmp.pigid=pig.id
                WHERE
                    createtime < :endtime
                AND (pig.status=:status)
                AND business_personid=:personid';

        $list = \Yii::$app->db->createCommand($sql)->bindValues([':personid'=>$personid,':status'=>BusinessPig::STATUS_AFTERIN,':endtime'=>$difftime])->queryAll();

        $recycle_day = SysSetting::getValue('inspection_cycle');
        return $this->render('inspection_detail',[
            'list' => $list,
            'recycle_day' =>$recycle_day
        ]);
    }

    /**
     * @return string
     * 巡检异常详情
     */
    public function actionNoInspectionDetail($personid)
    {
        $this->layout = false;
        $sql = 'select headpersonid as business_personid,headperson as business_person,pig.no,pig.batch,pig.liveryname from '.BusinessPig::tableName().' as pig where pig.status=:status and headpersonid=:personid and !exists(select id from '.NewBusinessInspection::tableName().' as ins where ins.pigid=pig.id and business_personid=pig.headpersonid)';

        $list = \Yii::$app->db->createCommand($sql)->bindValues([':personid'=>$personid,':status'=>BusinessPig::STATUS_AFTERIN])->queryAll();

        $recycle_day = SysSetting::getValue('inspection_cycle');
        return $this->render('inspection_detail',[
            'list' => $list,
            'recycle_day' =>$recycle_day
        ]);
    }


    /**
     *
     * 时间日历方式显示巡检记录
     */
    public function actionDatashowInspection($personid)
    {
        $this->layout = false;


        return $this->render('data_inspection',['personid'=>$personid]);
    }


    /**
     *
     * 获取巡检信息共日历读取
     */
    public function actionInsData()
    {
        $pid = \Yii::$app->request->get('pid');
        $start = \Yii::$app->request->get('start');
        $end = \Yii::$app->request->get('end');
        $createtime = new Expression('FROM_UNIXTIME(createtime,"%Y-%m-%d") as start');
        $ins = (new Query())->select([$createtime,'pig.no as title'])
            ->from(['ins'=>NewBusinessInspection::tableName()])
            ->leftJoin(['pig'=> BusinessPig::tableName()],'pig.id=ins.pigid')
            ->where('business_personid=:pid and createtime>=:start and createtime <= :end',[
                ':pid'=>intval($pid),
                ':start'=>intval($start),
                ':end' => intval($end)
            ])
            ->all();

//        var_dump($ins);exit;
        return \GuzzleHttp\json_encode($ins);
    }






    //防疫异常
    public function actionVaccine()
    {
        //将来多少天内有多少仔猪需要打疫苗
        $getday = \Yii::$app->request->get('day');
        if(empty($getday)){
            $days = 10*3600*24;
        }else{
            $days = intval($getday)*3600*24;
        }
        $stime = time()-$days;
        $etime = time()+$days;

        //取出这段时间的有哪些批次的仔猪需要打的疫苗
        $sql = 'SELECT batch,vaccinename,id FROM business_vaccine_set WHERE date >:stime AND date <:etime';
        $list = \Yii::$app->db->createCommand($sql)->bindValues([':stime'=>$stime,':etime'=>$etime])->queryAll();
        if(empty($list)){
            $list = [];
            return $this->dialog('最近10日没有需要防疫的仔猪');
        }

        $batch = [];
        $vacid = [];
        foreach ($list as $key => $row){
            $batch[] = $row['batch'];
            $vacid[] = $row['id'];
        }

        $batchs = implode(',',array_unique($batch));
        $vacids = implode(',',$vacid);

        unset($list);
        //有可能有些已经打了，查看哪些仔猪没有打疫苗
        //查询在上面的批次中，并且vaccineid没有在business_vaccine中的出现的仔猪
        $sql = 'select count(*) as num,pig.batch from '.BusinessPig::tableName().' as pig left join '.BusinessVaccine::tableName().' as vac on pig.id=vac.pigid where batch in('.$batchs.') and !exists(select 1 from '.BusinessVaccine::tableName().' as vac where vac.pigid=pig.id and vac.vaccineid in('.$vacids.')) group by pig.batch';
        $list = \Yii::$app->db->createCommand($sql)->queryAll();


        //取出每批总共有多少仔猪
        $batchcount = (new Query())->select('count(*) as total,batch')->from(BusinessPig::tableName())->groupBy('batch')->indexBy('batch')->all();
        foreach ($list as $key => $row){
            $list[$key]['total'] = intval($batchcount[$row['batch']]['total']);
            $list[$key]['hasvacc'] = intval($batchcount[$row['batch']]['total']-$row['num']);
            $list[$key]['show'] = [['name'=>'待防疫','value'=>intval($row['num'])],['name'=>'待防疫','value'=>intval($list[$key]['hasvacc'])]];
        }


        return $this->render('vaccine',[
            'list' => $list,
            'days' => $days,
        ]);
    }


    public function actionVaccinedetail($batch)
    {
        $this->layout = false;

        //将来多少天内有多少仔猪需要打疫苗
        $getday = \Yii::$app->request->get('days');
        if(empty($getday)){
            $days = 4*3600*24;
        }else{
            $days = intval($getday)*3600*24;
        }
        //一段时间内有多少周没有打疫苗
//        $days = 4*3600*24;
        $stime = time()-$days;
        $etime = time()+$days;

        //取出这段时间的有哪些批次的仔猪需要打的疫苗
        $sql = 'SELECT vaccinename,id FROM business_vaccine_set WHERE batch=:batch and date >:stime AND date <:etime';
        $list = \Yii::$app->db->createCommand($sql)->bindValues([':batch'=>$batch,':stime'=>$stime,':etime'=>$etime])->queryAll();

        //有可能有些已经打了，查看哪些仔猪没有打疫苗
        //查询在上面的批次中，并且vaccineid没有在business_vaccine中的出现的仔猪
        $vacc = [];
        foreach ($list as $key => $row){
            $sql = 'select pig.no,pig.id,(select vaccinename from '.BusinessVaccineSet::tableName().' where id=:vacid) as vaccinename from '.BusinessPig::tableName().' as pig left join '.BusinessVaccine::tableName().' as vac on pig.id=vac.pigid where pig.batch=:batch and !exists(select 1 from '.BusinessVaccine::tableName().' as vac where vac.pigid=pig.id and vac.vaccineid=:vacid)';
            $vacc[] = \Yii::$app->db->createCommand($sql)->bindValues([':batch'=>$batch,':vacid'=>$row['id']])->queryAll();
        }
        unset($list);
        $vacs = [];
        foreach ($vacc as $key => $row){
            foreach ($row as $item => $value) {
                $vacs[$value['no']][] = $value;
            }
        }
        unset($vacc);

        return $this->render('vaccinedetail',[
            'list' => $vacs,
            'batch' => $batch,
        ]);

    }




    //发病率
    public function actionDisease()
    {

        //表格数据查询
        //死亡数
        $diesql = 'SELECT count(*) FROM '.BusinessPig::tableName() . ' as dpig where dpig.batch=pig.batch and dpig.status=:status';
        //总数
        $totalsql = 'select count(*) from ' . BusinessPig::tableName() . ' where batch=pig.batch';

        $diseasesql = 'select count(disea.id) from ' . BusinessDisease::tableName() . ' disea left join '.BusinessPig::tableName().' depig on disea.pigid=depig.id where depig.batch=pig.batch and disea.step!=:diseastatus';

        $sql = 'select pig.batch,(' . $diesql . ') as die,('.$totalsql.') as total,('.$diseasesql.') as num from ' . BusinessPig::tableName() . ' as pig group by batch';
        $disea = \Yii::$app->db->createCommand($sql)->bindValues([':status' => BusinessPig::STATUS_DIEED,':diseastatus'=>BusinessDisease::RECOVERED])->queryAll();

        foreach ($disea as $item => $value) {
            $disea[$item]['persentdie'] = round((($value['die'] / $value['total']) * 100), 2) . '%';
            $disea[$item]['persentdisea'] = round((($value['num'] / $value['total']) * 100), 2) . '%';
            $disea[$item]['show'] = [['name' => '死亡', 'value' => $value['die']], ['name' => '生病', 'value' => $value['num']], ['name' => '健康', 'value' => $value['total'] - $value['num']]];

        }


        //取出疾病统计,柱形图
        $query = (new Query())->select('count(id) as num,type')
            ->from(BusinessDisease::tableName() . ' as dis')
            ->groupBy('type');

        $data['type'] = [];
        $data['num'] = [];
        foreach ($query->each(400) as $key => $rows) {
            $data['type'][] = $rows['type'];
            $data['num'][] = $rows['num'];
        }
        unset($query);

        return $this->render('disease', [
            'disea' => $disea,
            'data' => $data,
        ]);
    }



    public function actionSendmsg()
    {
        $personid = \Yii::$app->request->post('personid');

        $person = (new Query())->select('name,openid')->from(BusinessPerson::tableName())->where('id=:id',[':id'=>$personid])->one();

        $staff = \Yii::$app->wechat->staff; // 客服管理
        $staff->message(new Text(['content' => '您好！'.$person['name'].'，您尚有仔猪未巡检，请及时巡检']))->to($person['openid'])->send();
    }

}




